package com.ecommerce.dao;

import com.ecommerce.model.InventoryHistory;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class InventoryDAO {

    // 在 InventoryDAO.java 中优化
    public boolean recordInventoryChange(InventoryHistory history) {
        String sql = "INSERT INTO inventory_history (product_id, change_type, change_quantity, " +
                "previous_stock, new_stock, reason, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 移除超时设置，使用默认值
            // stmt.setQueryTimeout(10); // 注释掉这行

            stmt.setInt(1, history.getProductId());
            stmt.setString(2, history.getChangeType());
            stmt.setInt(3, history.getChangeQuantity());
            stmt.setInt(4, history.getPreviousStock());
            stmt.setInt(5, history.getNewStock());
            stmt.setString(6, history.getReason());
            stmt.setInt(7, history.getCreatedBy());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("记录库存变更失败: " + e.getMessage());
            // 返回true，不让历史记录失败影响主流程
            return true;
        }
    }

    public List<InventoryHistory> getInventoryHistoryByProduct(int productId) {
        List<InventoryHistory> history = new ArrayList<>();
        String sql = "SELECT ih.*, p.name as product_name, u.username as created_by_name " +
                "FROM inventory_history ih " +
                "JOIN products p ON ih.product_id = p.id " +
                "LEFT JOIN users u ON ih.created_by = u.id " +
                "WHERE ih.product_id = ? ORDER BY ih.created_at DESC";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, productId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                history.add(extractInventoryHistoryFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("获取库存历史失败: " + e.getMessage());
        }

        return history;
    }

    public List<InventoryHistory> getAllInventoryHistory() {
        List<InventoryHistory> history = new ArrayList<>();
        String sql = "SELECT ih.*, p.name as product_name, u.username as created_by_name " +
                "FROM inventory_history ih " +
                "JOIN products p ON ih.product_id = p.id " +
                "LEFT JOIN users u ON ih.created_by = u.id " +
                "ORDER BY ih.created_at DESC LIMIT 100";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                history.add(extractInventoryHistoryFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("获取所有库存历史失败: " + e.getMessage());
        }

        return history;
    }

    // 修复：更新低库存阈值 - 确保有WHERE条件
    public boolean updateLowStockThreshold(int productId, int threshold) {
        String sql = "UPDATE products SET low_stock_threshold = ? WHERE id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, threshold);
            stmt.setInt(2, productId); // WHERE条件

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("更新低库存阈值失败: " + e.getMessage());
            return false;
        }
    }

    private InventoryHistory extractInventoryHistoryFromResultSet(ResultSet rs) throws SQLException {
        InventoryHistory history = new InventoryHistory();
        history.setId(rs.getInt("id"));
        history.setProductId(rs.getInt("product_id"));
        history.setProductName(rs.getString("product_name"));
        history.setChangeType(rs.getString("change_type"));
        history.setChangeQuantity(rs.getInt("change_quantity"));
        history.setPreviousStock(rs.getInt("previous_stock"));
        history.setNewStock(rs.getInt("new_stock"));
        history.setReason(rs.getString("reason"));
        history.setCreatedBy(rs.getInt("created_by"));
        history.setCreatedByName(rs.getString("created_by_name"));
        history.setCreatedAt(rs.getTimestamp("created_at"));
        return history;
    }
}